/**
 * 学生选课系统数据库初始化
 * 使用SQLite作为数据库
 */

const sqlite3 = require('sqlite3').verbose();
const path = require('path');
const fs = require('fs');

// 确保数据库目录存在
const dbDir = path.join(__dirname, 'data');
if (!fs.existsSync(dbDir)) {
  fs.mkdirSync(dbDir);
}

// 数据库文件路径
const dbPath = path.join(dbDir, 'course_selection.db');

// 创建数据库连接
const db = new sqlite3.Database(dbPath, (err) => {
  if (err) {
    console.error('数据库连接失败:', err.message);
  } else {
    console.log('已连接到SQLite数据库');
    // 初始化数据库表
    initDatabase();
  }
});

/**
 * 初始化数据库表结构
 */
function initDatabase() {
  // 开启外键约束
  db.run('PRAGMA foreign_keys = ON');

  // 创建用户表 - 用于系统登录
  db.run(`
    CREATE TABLE IF NOT EXISTS users (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      username TEXT NOT NULL UNIQUE,
      password TEXT NOT NULL,
      role TEXT NOT NULL CHECK(role IN ('admin', 'teacher', 'student')),
      created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
      last_login TIMESTAMP
    )
  `, (err) => {
    if (err) {
      console.error('创建用户表失败:', err.message);
    } else {
      console.log('用户表创建成功或已存在');
    }
  });

  // 创建学生表
  db.run(`
    CREATE TABLE IF NOT EXISTS students (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      student_id TEXT NOT NULL UNIQUE,
      name TEXT NOT NULL,
      gender TEXT CHECK(gender IN ('男', '女')),
      age INTEGER,
      class TEXT,
      department TEXT,
      major TEXT,
      user_id INTEGER,
      FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
    )
  `, (err) => {
    if (err) {
      console.error('创建学生表失败:', err.message);
    } else {
      console.log('学生表创建成功或已存在');
    }
  });

  // 创建教师表
  db.run(`
    CREATE TABLE IF NOT EXISTS teachers (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      teacher_id TEXT NOT NULL UNIQUE,
      name TEXT NOT NULL,
      gender TEXT CHECK(gender IN ('男', '女')),
      title TEXT,
      department TEXT,
      user_id INTEGER,
      FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
    )
  `, (err) => {
    if (err) {
      console.error('创建教师表失败:', err.message);
    } else {
      console.log('教师表创建成功或已存在');
    }
  });

  // 创建课程表
  db.run(`
    CREATE TABLE IF NOT EXISTS courses (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      course_id TEXT NOT NULL UNIQUE,
      name TEXT NOT NULL,
      credit REAL NOT NULL,
      hours INTEGER NOT NULL,
      description TEXT,
      max_students INTEGER DEFAULT 50,
      teacher_id INTEGER,
      semester TEXT NOT NULL,
      status TEXT DEFAULT 'open' CHECK(status IN ('open', 'closed')),
      created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
      FOREIGN KEY (teacher_id) REFERENCES teachers(id) ON DELETE SET NULL
    )
  `, (err) => {
    if (err) {
      console.error('创建课程表失败:', err.message);
    } else {
      console.log('课程表创建成功或已存在');
    }
  });

  // 创建选课记录表
  db.run(`
    CREATE TABLE IF NOT EXISTS enrollments (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      student_id INTEGER NOT NULL,
      course_id INTEGER NOT NULL,
      enrollment_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
      grade REAL,
      status TEXT DEFAULT 'enrolled' CHECK(status IN ('enrolled', 'dropped', 'completed')),
      FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE,
      FOREIGN KEY (course_id) REFERENCES courses(id) ON DELETE CASCADE,
      UNIQUE(student_id, course_id)
    )
  `, (err) => {
    if (err) {
      console.error('创建选课记录表失败:', err.message);
    } else {
      console.log('选课记录表创建成功或已存在');
    }
  });

  // 创建课程时间表
  db.run(`
    CREATE TABLE IF NOT EXISTS course_schedules (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      course_id INTEGER NOT NULL,
      day_of_week INTEGER NOT NULL CHECK(day_of_week BETWEEN 1 AND 7),
      start_time TEXT NOT NULL,
      end_time TEXT NOT NULL,
      classroom TEXT,
      FOREIGN KEY (course_id) REFERENCES courses(id) ON DELETE CASCADE
    )
  `, (err) => {
    if (err) {
      console.error('创建课程时间表失败:', err.message);
    } else {
      console.log('课程时间表创建成功或已存在');
    }
  });
}

/**
 * 关闭数据库连接
 */
function closeDatabase() {
  db.close((err) => {
    if (err) {
      console.error('关闭数据库连接失败:', err.message);
    } else {
      console.log('数据库连接已关闭');
    }
  });
}

// 导出数据库对象和函数
module.exports = {
  db,
  closeDatabase
};
